package com.fireline.beauty.jdbc;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;

import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.sun.rowset.CachedRowSetImpl;
import com.zgw.fireline.base.DatasetDefine;
import com.zgw.fireline.base.IDataBaseProvide;

@SuppressWarnings("unchecked")
public class SqliteJdbcImpl extends JdbcDaoSupport implements IDataBaseProvide {
	public static SqliteJdbcImpl INSTANCE = new SqliteJdbcImpl();

	public SqliteJdbcImpl() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource(
				"org.sqlite.JDBC",
				"jdbc:sqlite:F:/emrWorkspace/com.fireline.beauty/FlBeauty.s3db",
				"", "");
		setDataSource(dataSource);
	}

	public CachedRowSet queryForDefine(String defineKey, Object[] params) {
		DatasetDefine define = getDataset(defineKey);
		if (define == null) {
			throw new RuntimeException("找不到指定数据集" + defineKey + "'");
		}
		String sql = define.getCommand();
		return queryForSql(sql, params);

	}

	public CachedRowSet queryForSql(final String sql, final Object[] params) {
		List list = new ArrayList();

		Object rowset = getJdbcTemplate().query(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection arg0)
					throws SQLException {
				PreparedStatement p = arg0.prepareStatement(sql);
				int i = 0;
				for (Object obj : params) {
					i++;
					if (obj instanceof Date) {
						p.setDate(i, new java.sql.Date(((Date) obj).getTime()));
					} else if ("".equals(obj)) {
						p.setObject(i, null);
					} else if (obj instanceof Boolean) {
						p.setBoolean(i, (Boolean) obj);
					} else {
						p.setObject(i, obj);
					}
				}
				return p;
			}
		}, new CachedRowSetExtractor());
		// Object rowset = getJdbcTemplate().query(sql, list.toArray(),
		// new CachedRowSetExtractor());
		return (CachedRowSet) rowset;

	}

	private class CachedRowSetExtractor implements ResultSetExtractor {
		@Override
		public Object extractData(ResultSet arg0) throws SQLException,
				DataAccessException {
			CachedRowSetImpl set = new CachedRowSetImpl();
			set.populate(arg0);
			return set;
		}

	}

	public int getParameterCount(String sql) {
		Connection conn = null;
		PreparedStatement meta = null;
		try {
			conn = getConnection();
			meta = conn.prepareStatement(sql);
			ParameterMetaData param = meta.getParameterMetaData();
			return param.getParameterCount();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				meta.close();
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	public int[] execute(String[] command, Map<Integer, Object[]> params) {
		Connection conn = null;
		int[] result = new int[command.length];
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			for (int i = 0; i < command.length; i++) {
				PreparedStatement statment = conn.prepareStatement(command[i]);
				Object[] p = params.get(i);
				for (int k = 0; k < p.length; k++) {
					if (p[k] instanceof Boolean) {
						statment.setObject(k + 1, (Boolean) p[k] ? 1 : 0);
					} else {
						statment.setObject(k + 1, p[k]);
					}
				}
				result[i] = statment.execute() ? 1 : 0;
				statment.close();
			}
			conn.commit();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				LogFactory.getLog(this.getClass()).error("连接关闭异常", e);
			}
		}
		return result;
	}

	@Override
	public List<DatasetDefine> getAllDataset() {
		return getJdbcTemplate().query("select * from DatasetDefine",
				new ObjectMapper(DatasetDefine.class));
	}

	@Override
	public boolean saveDataset(DatasetDefine define) {
		String sql = "";
		ArrayList<String> param = new ArrayList<String>();
		SqlRowSet result = getJdbcTemplate()
				.queryForRowSet(
						"select * from DatasetDefine where id='"
								+ define.getId() + "'");
		if (result.next()) {
			sql = "update DatasetDefine set name=?,command=?,typeId=? where id=?";
			param.add(define.getName());
			param.add(define.getCommand());
			param.add(define.getTypeId());
			param.add(define.getId());
		} else {
			sql = "insert into DatasetDefine (id,name,command,typeId) values (?,?,?,?)";
			param.add(define.getId());
			param.add(define.getName());
			param.add(define.getCommand());
			param.add(define.getTypeId());
		}
		return getJdbcTemplate().update(sql, param.toArray()) > 0;
	}

	@Override
	public boolean removeDataset(String defineKey) {
		String sql = "delete from DatasetDefine where id='" + defineKey + "'";
		return getJdbcTemplate().update(sql) > 0;
	}

	@Override
	public DatasetDefine getDataset(String defineKey) {
		String sql = "select * from DatasetDefine where id='" + defineKey + "'";
		return (DatasetDefine) getJdbcTemplate().queryForObject(sql,
				new ObjectMapper(DatasetDefine.class));
	}

	public static void main(String[] args) {
		INSTANCE.queryForSql("select * from DatasetDefine where id=?",
				new Object[] { 1 }).size();
		System.out.println(INSTANCE.getAllDataset());
		INSTANCE.getParameterCount("select * from DatasetDefine where id=?");
	}
}
